﻿
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

// Taken from http://solidcoding.blogspot.com/2008/01/linq-to-excel-provider-25.html
// Owner is K Jacobson (solidcoding@gmail.com)
namespace LinqToExcel
{
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class ExcelColumnAttribute : Attribute
    {
        private PropertyInfo myPropInfo;

        public ExcelColumnAttribute()
        {
            Name = string.Empty;
            Storage = string.Empty;
        }

        public string Name { get; set; }

        public string Storage { get; set; }

        internal PropertyInfo GetProperty()
        {
            return myPropInfo;
        }

        internal void SetProperty(PropertyInfo propInfo)
        {
            myPropInfo = propInfo;
        }

        internal string GetSelectColumn()
        {
            return Name == string.Empty ? myPropInfo.Name : Name;
        }

        internal string GetStorageName()
        {
            return Storage == string.Empty ? myPropInfo.Name : Storage;
        }

        internal bool IsFieldStorage()
        {
            return string.IsNullOrEmpty(Storage) == false;
        }
    }

    public class ExcelSheetAttribute : Attribute
    {
        public string Name { get; set; }
    }

    public class ExcelMapReader
    {
        public static string GetSheetName(Type t)
        {
            var attr = t.GetCustomAttributes(typeof (ExcelSheetAttribute), true);
            if (attr.Length == 0)
            {
                throw new InvalidOperationException("ExcelSheetAttribute not found on type " + t.FullName);
            }
            var sheet = (ExcelSheetAttribute) attr[0];
            return sheet.Name == string.Empty ? t.Name : sheet.Name;
        }

        public static List<ExcelColumnAttribute> GetColumnList(Type t)
        {
            var lst = new List<ExcelColumnAttribute>();
            foreach (var propInfo in t.GetProperties())
            {
                var attr = propInfo.GetCustomAttributes(typeof (ExcelColumnAttribute), true);
                if (attr.Length <= 0) continue;
                var col = (ExcelColumnAttribute) attr[0];
                col.SetProperty(propInfo);
                lst.Add(col);
            }
            return lst;
        }
    }

    public class ExcelConnectionString
    {
        internal static string GetConnectionString(string pFilePath)
        {
            string strConnectionString;
            var strExcelExt = Path.GetExtension(pFilePath);

            switch (strExcelExt)
            {
                case ".xls":
                    strConnectionString =
                        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties= ""Excel 8.0;HDR=YES;""";
                    break;
                case ".xlsm":
                case ".xlsx":
                    strConnectionString =
                        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""";
                    break;
                default:
                    throw new NotSupportedException(pFilePath);
            }

            return string.Format(strConnectionString, pFilePath);
        }
    }

    public class ExcelSheet<T> : IEnumerable<T>
    {
        private readonly ExcelProvider myProvider;
        private readonly List<T> myRows;

        internal ExcelSheet(ExcelProvider provider)
        {
            this.myProvider = provider;
            myRows = new List<T>();
        }

        #region IEnumerable<T> Members

        public IEnumerator<T> GetEnumerator()
        {
            Load();
            return myRows.GetEnumerator();
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            Load();
            return myRows.GetEnumerator();
        }

        #endregion

        private static string BuildSelect()
        {
            var sheet = ExcelMapReader.GetSheetName(typeof (T));
            var builder = new StringBuilder();
            foreach (var col in ExcelMapReader.GetColumnList(typeof (T)))
            {
                if (builder.Length > 0)
                {
                    builder.Append(", ");
                }
                builder.AppendFormat("[{0}]", col.GetSelectColumn());
            }
            builder.Append(" FROM [");
            builder.Append(sheet);
            builder.Append("$]");
            return "SELECT " + builder;
        }

        private static T CreateInstance()
        {
            return Activator.CreateInstance<T>();
        }

        private void Load()
        {
            var connectionString = ExcelConnectionString.GetConnectionString(myProvider.Filepath);
            var columns = ExcelMapReader.GetColumnList(typeof (T));
            myRows.Clear();
            
            using (var conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = BuildSelect();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            T item = CreateInstance();
                            var pm = new List<PropertyManager>();
                            foreach (ExcelColumnAttribute col in columns)
                            {
                                object val = reader[col.GetSelectColumn()];
                                if (val is DBNull)
                                {
                                    val = null;
                                }
                                if (col.IsFieldStorage())
                                {
                                    var fi = typeof (T).GetField(col.GetStorageName(),
                                                                       BindingFlags.GetField | BindingFlags.NonPublic |
                                                                       BindingFlags.Instance | BindingFlags.SetField);
                                    
                                    //TomKom 3/13/2009 add change type conversion.
                                    fi.SetValue(item, Convert.ChangeType(val, fi.FieldType));
                                }
                                else
                                {
                                    //TomKom 3/13/2009 add change type conversion.
                                    typeof (T).GetProperty(col.GetStorageName()).SetValue(item,
                                                                                          Convert.ChangeType(val,
                                                                                                             typeof (T).
                                                                                                                 GetProperty
                                                                                                                 (col.
                                                                                                                      GetStorageName
                                                                                                                      ())
                                                                                                                 .
                                                                                                                 PropertyType),
                                                                                          null);
                                }
                                pm.Add(new PropertyManager(col.GetProperty().Name, val));
                            }
                            myRows.Add(item);
                            AddToTracking(item, pm);
                        }
                    }
                }
            }
        }

        private void AddToTracking(Object obj, List<PropertyManager> props)
        {
            myProvider.ChangeSet.AddObject(new ObjectState(obj, props));
        }

        public void InsertOnSubmit(T entity)
        {
            // Add to tracking
            myProvider.ChangeSet.InsertObject(entity);
        }

        public void DeleteOnSubmit(T entity)
        {
            myProvider.ChangeSet.DeleteObject(entity);
        }
    }

    public class ExcelProvider
    {
        public ExcelProvider()
        {
            ChangeSet = new ChangeSet();
        }

        internal ChangeSet ChangeSet { get; private set; }

        internal string Filepath { get; private set; }

        public static ExcelProvider Create(string filePath)
        {
            var provider = new ExcelProvider {Filepath = filePath};
            return provider;
        }

        public ExcelSheet<T> GetSheet<T>()
        {
            return new ExcelSheet<T>(this);
        }

        public void SubmitChanges()
        {
            string connectionString = ExcelConnectionString.GetConnectionString(Filepath);
            using (var conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                foreach (ObjectState os in ChangeSet.ChangedObjects)
                {
                    using (OleDbCommand cmd = conn.CreateCommand())
                    {
                        if (os.ChangeState == ChangeState.Deleted)
                        {
                            BuildDeleteClause(cmd, os);
                        }
                        if (os.ChangeState == ChangeState.Updated)
                        {
                            BuildUpdateClause(cmd, os);
                        }
                        if (os.ChangeState == ChangeState.Inserted)
                        {
                            BuildInsertClause(cmd, os);
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        public void BuildInsertClause(OleDbCommand cmd, ObjectState objState)
        {
            string sheet = ExcelMapReader.GetSheetName(objState.Entity.GetType());
            var builder = new StringBuilder();
            builder.Append("INSERT INTO [");
            builder.Append(sheet);
            builder.Append("$]");
            var columns = new StringBuilder();
            var values = new StringBuilder();
            foreach (ExcelColumnAttribute col in ExcelMapReader.GetColumnList(objState.Entity.GetType()))
            {
                if (columns.Length > 0)
                {
                    columns.Append(", ");
                    values.Append(", ");
                }
                columns.AppendFormat("[{0}]", col.GetSelectColumn());
                string paraNum = "@x" + cmd.Parameters.Count.ToString();
                values.Append(paraNum);
                object val = col.GetProperty().GetValue(objState.Entity, null);
                var para = new OleDbParameter(paraNum, val);
                cmd.Parameters.Add(para);
            }
            cmd.CommandText = builder.ToString() + "(" + columns.ToString() + ") VALUES (" +
                              values.ToString() + ")";
        }

        public void BuildUpdateClause(OleDbCommand cmd, ObjectState objState)
        {
            var builder = new StringBuilder();
            string sheet = ExcelMapReader.GetSheetName(objState.Entity.GetType());
            builder.Append("UPDATE [");
            builder.Append(sheet);
            builder.Append("$] SET ");
            var changeBuilder = new StringBuilder();
            List<ExcelColumnAttribute> cols = ExcelMapReader.GetColumnList(objState.Entity.GetType());
            List<ExcelColumnAttribute> changedCols =
                (from c in cols
                 join p in objState.ChangedProperties on c.GetProperty().Name equals p.PropertyName
                 where p.HasChanged == true
                 select c).ToList();
            foreach (ExcelColumnAttribute col in changedCols)
            {
                if (changeBuilder.Length > 0)
                {
                    changeBuilder.Append(", ");
                }
                string paraNum = "@x" + cmd.Parameters.Count.ToString();
                changeBuilder.AppendFormat("[{0}]", col.GetSelectColumn());
                changeBuilder.Append(" = ");
                changeBuilder.Append(paraNum);
                object val = col.GetProperty().GetValue(objState.Entity, null);
                var para = new OleDbParameter(paraNum, val);
                cmd.Parameters.Add(para);
            }
            builder.Append(changeBuilder.ToString());
            cmd.CommandText = builder.ToString();
            BuildWhereClause(cmd, objState);
        }

        public void BuildDeleteClause(OleDbCommand cmd, ObjectState objState)
        {
            var builder = new StringBuilder();
            string sheet = ExcelMapReader.GetSheetName(objState.Entity.GetType());
            builder.Append("DELETE FROM [");
            builder.Append(sheet);
            builder.Append("$]");
            cmd.CommandText = builder.ToString();
            BuildWhereClause(cmd, objState);
        }

        public void BuildWhereClause(OleDbCommand cmd, ObjectState objState)
        {
            var builder = new StringBuilder();
            List<ExcelColumnAttribute> cols = ExcelMapReader.GetColumnList(objState.Entity.GetType());
            foreach (ExcelColumnAttribute col in cols)
            {
                PropertyManager pm = objState.GetProperty(col.GetProperty().Name);
                if (builder.Length > 0)
                {
                    builder.Append(" and ");
                }

                builder.AppendFormat("[{0}]", col.GetSelectColumn());
                //fix from Andrew 4/2/08 to handle empty cells
                if (pm.OrginalValue == DBNull.Value)
                    builder.Append(" IS NULL");
                else
                {
                    builder.Append(" = ");
                    string paraNum = "@x" + cmd.Parameters.Count.ToString();
                    builder.Append(paraNum);
                    var para = new OleDbParameter(paraNum, pm.OrginalValue);
                    cmd.Parameters.Add(para);
                }
            }
            cmd.CommandText = cmd.CommandText + " WHERE " + builder.ToString();
        }
    }

    public class PropertyManager
    {
        private bool hasChanged;
        private object orginalValue;
        private string propertyName;

        public PropertyManager(string propName, object value)
        {
            propertyName = propName;
            orginalValue = value;
            hasChanged = false;
        }

        public string PropertyName
        {
            get { return propertyName; }
            set { propertyName = value; }
        }

        public object OrginalValue
        {
            get { return orginalValue; }
            set { orginalValue = value; }
        }

        public bool HasChanged
        {
            get { return hasChanged; }
            set { hasChanged = value; }
        }
    }

    public enum ChangeState
    {
        Retrieved,
        Updated,
        Inserted,
        Deleted
    }

    public class ObjectState
    {
        private object entity;
        private List<PropertyManager> propList;
        private ChangeState state;

        public ObjectState(object entity, List<PropertyManager> props)
        {
            this.entity = entity;
            propList = props;
            state = ChangeState.Retrieved;
            if (entity is INotifyPropertyChanged)
            {
                var i = (INotifyPropertyChanged) entity;
                i.PropertyChanged += new PropertyChangedEventHandler(i_PropertyChanged);
            }
        }

        public List<PropertyManager> Properties
        {
            get { return propList; }
        }

        public List<PropertyManager> ChangedProperties
        {
            get { return (from p in propList where p.HasChanged == true select p).ToList(); }
        }

        public ChangeState ChangeState
        {
            get { return state; }
            set { state = value; }
        }

        public Object Entity
        {
            get { return entity; }
        }

        public PropertyManager GetProperty(string propertyName)
        {
            return (from p in propList where p.PropertyName == propertyName select p).FirstOrDefault();
        }

        public void i_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            PropertyManager pm =
                (from p in propList where p.HasChanged == false && p.PropertyName == e.PropertyName select p).
                    FirstOrDefault();
            if (pm != null)
            {
                pm.HasChanged = true;
                if (state == ChangeState.Retrieved)
                    state = ChangeState.Updated;
            }
        }
    }

    public class ChangeSet
    {
        private List<ObjectState> trackedList;

        public ChangeSet()
        {
            trackedList = new List<ObjectState>();
        }

        public List<ObjectState> ChangedObjects
        {
            get { return (from c in trackedList where c.ChangeState != ChangeState.Retrieved select c).ToList(); }
        }

        public void AddObject(ObjectState objectState)
        {
            trackedList.Add(objectState);
        }

        public void InsertObject(Object obj)
        {
            foreach (ObjectState os in trackedList)
            {
                if (ReferenceEquals(os.Entity, obj))
                {
                    throw new InvalidOperationException("Object already in list");
                }
            }
            var osNew = new ObjectState(obj, new List<PropertyManager>());
            osNew.ChangeState = ChangeState.Inserted;
            trackedList.Add(osNew);
        }

        public void DeleteObject(Object obj)
        {
            ObjectState os =
                (from o in trackedList where ReferenceEquals(o.Entity, obj) == true select o).FirstOrDefault();
            if (os != null)
            {
                if (os.ChangeState == ChangeState.Inserted)
                {
                    trackedList.Remove(os);
                }
                else
                {
                    os.ChangeState = ChangeState.Deleted;
                }
            }
        }
    }
}